package com.idc.excel.dsw;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.LinkedHashMap;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;

public class DswCellWriteHandler implements CellWriteHandler {

	private static Map<Integer, String> formulaMap = new LinkedHashMap<>();
	private static Map<String, String> provinceFormulaMap = new LinkedHashMap<>();
	private static Map<String, String> cityFormulaMap = new LinkedHashMap<>();
	private static Map<Integer, String[]> replaceMap = new LinkedHashMap<>();

	static {
		replaceMap.put(7, new String[] { "I", "O" });
		replaceMap.put(8, new String[] { "G" });
		replaceMap.put(11, new String[] { "G" });
		replaceMap.put(12, new String[] { "G" });
		replaceMap.put(13, new String[] { "G" });
		replaceMap.put(14, new String[] { "G" });
		replaceMap.put(16, new String[] { "G" });
		replaceMap.put(17, new String[] { "G" });
		replaceMap.put(18, new String[] { "G" });
		replaceMap.put(19, new String[] { "G" });

		String filePath = "C:\\Users\\" + System.getProperty("user.name") + "\\Desktop\\CR\\gongshi.xlsx";
		try (InputStream in = new FileInputStream(filePath); Workbook workbook = new XSSFWorkbook(in)) {
			Sheet sheet = workbook.getSheetAt(3);
			for (Row row : sheet) {
				int rowNum = row.getRowNum();
				System.out.println("rowNum========>" + rowNum);
				if (rowNum > 0) {
					String provinceName = row.getCell(0).getStringCellValue();
					String provinceFormula = row.getCell(11).getCellFormula();
					String cityFormula = row.getCell(12).getCellFormula();
					provinceFormulaMap.put(provinceName, provinceFormula.replace("G" + (rowNum + 1), "G%d"));
					cityFormulaMap.put(provinceName, cityFormula.replace("G" + (rowNum + 1), "G%d"));
					// System.out.println(provinceName + "-->" + provinceFormula + "-->" + cityFormula);
					for (int i = 7; i <= 19; i++) {
						if (i != 9 && i != 10) {
							Cell cell = row.getCell(i);
							if (cell.getCellType() == CellType.FORMULA) {
								if (!formulaMap.containsKey(i)) {
									String formula = cell.getCellFormula();
									System.out.println("原始：" + formula);
									String[] replaceArr = replaceMap.get(i);
									for (String replaceChar : replaceArr) {
										formula = formula.replace(replaceChar + (rowNum + 1), replaceChar + "%d");
									}
									formulaMap.put(i, formula);
								}
							}
						}
					}
				}
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
		provinceFormulaMap.forEach((k, v) -> {
			System.out.println(k + "--->" + v);
		});
		cityFormulaMap.forEach((k, v) -> {
			System.out.println(k + "--->" + v);
		});
		formulaMap.forEach((k, v) -> {
			System.out.println(k + "--->" + v);
		});
	}

    @Override
	public void afterCellDispose(CellWriteHandlerContext context) {
		String provinceName = context.getRow().getCell(0).getStringCellValue();
		if (StringUtils.isEmpty(provinceName)) {
			return;
		}
		Cell cell = context.getCell();
		int columnIndex = cell.getColumnIndex();
		if (columnIndex >= 7 && columnIndex <= 19) {
			int rowNum = cell.getRow().getRowNum() + 1; // 转换为Excel行号
			String formula = null;
			if (rowNum >= 2) {
				if (columnIndex == 9) {
					formula = String.format(provinceFormulaMap.get(provinceName), rowNum, rowNum);
				} else if (columnIndex == 10) {
					formula = String.format(cityFormulaMap.get(provinceName), rowNum, rowNum);
				} else {
					if (columnIndex != 15) {
						try {
							formula = String.format(formulaMap.get(columnIndex), rowNum, rowNum, rowNum);
						} catch (Exception e) {
							System.out.println(columnIndex);
							throw new RuntimeException(e);
						}
					}
				}
				if (!StringUtils.isEmpty(formula)) {
					cell.setCellFormula(formula);
				}
			}
		}
        }

	public static void main(String[] args) {
		String a = "1111111%d22222";
		System.out.println(String.format(a, 999, 888, 222));
	}
}
